<?php
class ModelNewsInstall extends Model {	
	public function checkDB(){		
		/*Check Database*/ 	
		$this->checkBlogCategory();
		$this->checkBlogArticle();
		$this->checkAuthor();
		$this->checkDownload();
		$this->checkShoutbox();
		$this->checkPolls();
		/*checkField - Create Field if not exist*/ 
		$this->checkField('news_article','approved','tinyint(1) NOT NULL DEFAULT 0');
		$this->checkField('news_article','reference_url','varchar(255) COLLATE utf8_bin DEFAULT NULL');
		$this->checkField('news_article','reference_title','varchar(255) COLLATE utf8_bin DEFAULT NULL');
		$this->checkField('news_article','new_category','varchar(255) NOT NULL');
		$this->checkField('news_article','poll_id','int(11) NOT NULL');
		$this->checkField('news_article_description','sub_description','text COLLATE utf8_bin NOT NULL');
		$this->checkField('news_author','store_id','int(11) NOT NULL DEFAULT 0');
		$this->checkField('news_author','sort_order','int(11) NOT NULL DEFAULT 0');
		$this->checkField('news_author','firstname','varchar(32) COLLATE utf8_bin NOT NULL');
		$this->checkField('news_author','lastname','varchar(32) COLLATE utf8_bin NOT NULL');
		$this->checkField('news_author','author_description','longtext COLLATE utf8_bin NOT NULL');
		$this->checkField('news_author','email','varchar(96) COLLATE utf8_bin NOT NULL');
		$this->checkField('news_author','telephone','varchar(32) COLLATE utf8_bin NOT NULL');
		$this->checkField('news_author','website','varchar(128) COLLATE utf8_bin NOT NULL');
		$this->checkField('news_author','password','varchar(40) COLLATE utf8_bin NOT NULL');
		$this->checkField('news_author','salt','varchar(9) COLLATE utf8_bin NOT NULL');
		$this->checkField('news_author','address','varchar(128) COLLATE utf8_bin NOT NULL');
		$this->checkField('news_author','city','varchar(128) COLLATE utf8_bin NOT NULL');
		$this->checkField('news_author','country_id','int(11) NOT NULL DEFAULT 0');
		$this->checkField('news_author','zone_id','int(11) NOT NULL DEFAULT 0');
		$this->checkField('news_author','ip','varchar(40) COLLATE utf8_bin NOT NULL DEFAULT 0');
		$this->checkField('news_author','status','tinyint(1) NOT NULL');
		$this->checkField('news_author','approved','tinyint(1) NOT NULL');
		$this->checkField('news_author','token','varchar(255) COLLATE utf8_bin NOT NULL');
		$this->checkField('news_author','newsletter','tinyint(1) NOT NULL DEFAULT 0');
		$this->checkField('news_article_image','language_id','int(11) NOT NULL');
		$this->checkField('news_article_image','image_description','varchar(255) COLLATE utf8_bin');
		$this->checkField('category','related_id','int(11) NOT NULL');		
		
	}
	
	/*Create Field if not exist*/ 
	public function checkField($table,$field,$type) {	
		$exists = false;
		$query=$this->db->query("SHOW columns FROM `".DB_PREFIX.$table."`");	
		foreach($query->rows as $column){
			if($column['Field'] == $field){
			 $exists=true;
			}
		}
		if(!$exists){
		$this->db->query("ALTER TABLE ".DB_PREFIX.$table." ADD ".$this->db->escape($field)." ".$this->db->escape($type));
		}
	}
	/*Create Article Database*/ 
	public function checkBlogArticle() {
		$news_article = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."news_article` (
			  `article_id` int(11) NOT NULL AUTO_INCREMENT,
			  `model` varchar(64) COLLATE utf8_bin NOT NULL,
			  `image` varchar(255) COLLATE utf8_bin DEFAULT NULL,
			  `author_id` int(11) NOT NULL,
			  `poll_id` int(11) NOT NULL,
			  `sort_order` int(11) NOT NULL DEFAULT '0',
			  `status` tinyint(1) NOT NULL DEFAULT '0',
			  `date_added` datetime NOT NULL,
			  `date_modified` datetime NOT NULL,
			  `new_category` varchar(255) COLLATE utf8_bin NOT NULL,
			  `reference_url` varchar(255) COLLATE utf8_bin DEFAULT NULL,
			  `reference_title` varchar(255) COLLATE utf8_bin DEFAULT NULL,
			  `viewed` int(5) NOT NULL DEFAULT '0',
			  PRIMARY KEY (`article_id`)
			) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;";		
			$news_article_description = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."news_article_description` (
			  `article_id` int(11) NOT NULL AUTO_INCREMENT,
			  `language_id` int(11) NOT NULL,
			  `name` varchar(255) COLLATE utf8_bin NOT NULL,
			  `description` longtext COLLATE utf8_bin NOT NULL,
			  `meta_description` varchar(255) COLLATE utf8_bin NOT NULL,
			  `meta_keyword` varchar(255) COLLATE utf8_bin NOT NULL,
			  `tag` text COLLATE utf8_bin NOT NULL,
			  `h1` varchar(255) COLLATE utf8_bin NOT NULL,
			  `h2` varchar(255) COLLATE utf8_bin NOT NULL,
			  `h3` varchar(255) COLLATE utf8_bin NOT NULL,
			  `h3_p` text COLLATE utf8_bin NOT NULL,
			  `h4` varchar(255) COLLATE utf8_bin NOT NULL,
			  `h4_p` text COLLATE utf8_bin NOT NULL,
			  `h5` varchar(255) COLLATE utf8_bin NOT NULL,
			  `h6` varchar(255) COLLATE utf8_bin NOT NULL,
			  `content_l1` varchar(255) COLLATE utf8_bin NOT NULL,
			  `content_l2` varchar(255) COLLATE utf8_bin NOT NULL,
			  `content_l3` text COLLATE utf8_bin NOT NULL,
			  `content_l4` text COLLATE utf8_bin NOT NULL,
			  `content_r1` varchar(255) COLLATE utf8_bin NOT NULL,
			  `content_r2` varchar(255) COLLATE utf8_bin NOT NULL,
			  `content_r3` text COLLATE utf8_bin NOT NULL,
			  `content_r4` text COLLATE utf8_bin NOT NULL,
			  PRIMARY KEY (`article_id`,`language_id`),
			  KEY `name` (`name`),
			  FULLTEXT KEY `description` (`description`),
			  FULLTEXT KEY `tag` (`tag`)
			) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;";		
		
		$news_article_image = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."news_article_image` (
			  `article_image_id` int(11) NOT NULL AUTO_INCREMENT,
			  `article_id` int(11) NOT NULL,
			  `image` varchar(255) COLLATE utf8_bin DEFAULT NULL,
			  `sort_order` int(3) NOT NULL DEFAULT '0',
			  `language_id` int(11) NOT NULL,
			  `image_description` varchar(255) COLLATE utf8_bin,
			  PRIMARY KEY (`article_image_id`)
			) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1;";
		$news_related_article = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."news_related_article` (
			  `article_id` int(11) NOT NULL,
			  `article_related_id` int(11) NOT NULL,
			  PRIMARY KEY (`article_id`,`article_related_id`)
			) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;";
		$news_related_product = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."news_related_product` (
			  `article_id` int(11) NOT NULL,
			  `product_related_id` int(11) NOT NULL,
			  PRIMARY KEY (`article_id`,`product_related_id`)
			) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;";
		$news_article_to_category = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."news_article_to_category` (
			  `article_id` int(11) NOT NULL,
			  `article_category_id` int(11) NOT NULL,
			  PRIMARY KEY (`article_id`,`article_category_id`)
			) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;";
		$news_article_to_download = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."news_article_to_download` (
			  `article_id` int(11) NOT NULL,
			  `download_id` int(11) NOT NULL,
			  PRIMARY KEY (`article_id`,`download_id`)
			) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;";
		$news_article_to_layout = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."news_article_to_layout` (
			  `article_id` int(11) NOT NULL,
			  `store_id` int(11) NOT NULL,

			  `layout_id` int(11) NOT NULL,
			  PRIMARY KEY (`article_id`,`store_id`)
			) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;";			
		$news_article_to_store = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."news_article_to_store` (
		  `article_id` int(11) NOT NULL,
		  `store_id` int(11) NOT NULL DEFAULT '0',
		  PRIMARY KEY (`article_id`,`store_id`)
		) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;";	
		
		$news_comment = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."news_comment` (
			  `comment_id` int(11) NOT NULL AUTO_INCREMENT,
			  `article_id` int(11) NOT NULL,
			  `customer_id` int(11) NOT NULL,
			  `author` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
			  `text` text COLLATE utf8_bin NOT NULL,
			  `rating` int(1) NOT NULL,
			  `status` tinyint(1) NOT NULL,
			  `date_added` datetime NOT NULL,
			  `date_modified` datetime NOT NULL,
			  PRIMARY KEY (`comment_id`),
			  KEY `article_id` (`article_id`)
			) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1;";	
		$this->db->query($news_article);
		$this->db->query($news_article_description);
		$this->db->query($news_article_image);
		$this->db->query($news_related_article);
		$this->db->query($news_related_product);
		$this->db->query($news_article_to_category);
		$this->db->query($news_article_to_download);
		$this->db->query($news_article_to_layout);
		$this->db->query($news_article_to_store);		
		$this->db->query($news_comment);		
	}
	
	/*Create Blog Category Database*/ 
	public function checkBlogCategory() {
		$news_category = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."news_category` (
			  `article_category_id` int(11) NOT NULL AUTO_INCREMENT,`image` varchar(255) COLLATE utf8_bin DEFAULT NULL,`parent_id` int(11) NOT NULL DEFAULT '0',`top` tinyint(1) NOT NULL,`column` int(3) NOT NULL,`sort_order` int(3) NOT NULL DEFAULT '0',`status` tinyint(1) NOT NULL,`date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',`date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',PRIMARY KEY (`article_category_id`)
			) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1;";
		$news_category_description = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."news_category_description` (
			  `article_category_id` int(11) NOT NULL,`language_id` int(11) NOT NULL,`name` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',`description` text COLLATE utf8_bin NOT NULL,`meta_description` varchar(255) COLLATE utf8_bin NOT NULL,`meta_keyword` varchar(255) COLLATE utf8_bin NOT NULL,PRIMARY KEY (`article_category_id`,`language_id`),KEY `name` (`name`)
			) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;";			
		$news_category_to_layout = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."news_category_to_layout` (
			  `article_category_id` int(11) NOT NULL,`store_id` int(11) NOT NULL,`layout_id` int(11) NOT NULL,PRIMARY KEY (`article_category_id`,`store_id`)
			) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;";	
		$news_category_to_store = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."news_category_to_store` (
			  `article_category_id` int(11) NOT NULL,`store_id` int(11) NOT NULL,PRIMARY KEY (`article_category_id`,`store_id`)
			) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;";	
		
		$this->db->query($news_category);
		$this->db->query($news_category_description);	
		$this->db->query($news_category_to_layout);	
		$this->db->query($news_category_to_store);	
		
	}
	/*Create Author Database*/ 
	public function checkAuthor(){		
		$news_author = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."news_author` (
		  `author_id` int(11) NOT NULL AUTO_INCREMENT,
		  `author` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
		  `image` varchar(255) COLLATE utf8_bin DEFAULT NULL,
		  `store_id` int(11) NOT NULL DEFAULT '0',
		  `firstname` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
		  `lastname` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
			`author_description` longtext COLLATE utf8_bin NOT NULL,
		  `email` varchar(96) COLLATE utf8_bin NOT NULL DEFAULT '',
		  `telephone` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
		  `website` varchar(128) COLLATE utf8_bin NOT NULL,
		  `password` varchar(40) COLLATE utf8_bin NOT NULL DEFAULT '',
		  `salt` varchar(9) COLLATE utf8_bin NOT NULL DEFAULT '',
		  `address` varchar(128) COLLATE utf8_bin NOT NULL,
		  `city` varchar(128) COLLATE utf8_bin NOT NULL,
		  `country_id` int(11) NOT NULL DEFAULT '0',
		  `zone_id` int(11) NOT NULL DEFAULT '0',
		  `ip` varchar(40) COLLATE utf8_bin NOT NULL DEFAULT '0',
		  `status` tinyint(1) NOT NULL,
		  `approved` tinyint(1) NOT NULL,
		  `token` varchar(255) COLLATE utf8_bin NOT NULL,
		  `date_added` datetime NOT NULL,
		  `newsletter` tinyint(1) NOT NULL DEFAULT '0',
		`sort_order` int(11) NOT NULL DEFAULT '0',
		  PRIMARY KEY (`author_id`)
		) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1;";	
		$news_author_ip = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."news_author_ip` (
		  `author_ip_id` int(11) NOT NULL AUTO_INCREMENT,
		  `author_id` int(11) NOT NULL,
		  `ip` varchar(40) COLLATE utf8_bin NOT NULL,
		  `date_added` datetime NOT NULL,
		  PRIMARY KEY (`author_ip_id`),
		  KEY `ip` (`ip`)
		) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;";
		$news_author_ip_blacklist = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."news_author_ip_blacklist` (
		  `author_ip_blacklist_id` int(11) NOT NULL AUTO_INCREMENT,
		  `ip` varchar(40) COLLATE utf8_bin NOT NULL,
		  PRIMARY KEY (`author_ip_blacklist_id`),
		  KEY `ip` (`ip`)
		) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;";
		
		$this->db->query($news_author);
		$this->db->query($news_author_ip);
		$this->db->query($news_author_ip_blacklist);
	}
	/*Create Download Database*/ 
	public function checkDownload(){		
		$news_download = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."news_download` (
			  `download_id` int(11) NOT NULL AUTO_INCREMENT,
			  `filename` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '',
			  `mask` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '',
			  `auth_key` varchar(64) COLLATE utf8_bin NOT NULL,
			  `remaining` int(11) NOT NULL DEFAULT '0',
			  `date_added` datetime NOT NULL,
			  PRIMARY KEY (`download_id`)
			) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;";	
		$news_download_description = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."news_download_description` (
				  `download_id` int(11) NOT NULL,
				  `language_id` int(11) NOT NULL,
				  `name` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
				  PRIMARY KEY (`download_id`,`language_id`)
				) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;";	
		$this->db->query($news_download);	
		$this->db->query($news_download_description);	
	}
	/*Create Shoutbox Database*/ 
	public function checkShoutbox(){		
		$create_shoutbox = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."shoutbox` (
		`shout_id` int(11) NOT NULL auto_increment, 
		`customer_id` int(11) NOT NULL, 
		`author` varchar(64) NOT NULL, 
		`message` TEXT NOT NULL, 
		`status` int(1) NOT NULL default '1',  
		`date_added` datetime NOT NULL default '0000-00-00 00:00:00', 
		`date_modified` datetime NOT NULL default '0000-00-00 00:00:00', 
		PRIMARY KEY  (`shout_id`)
		) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;";	
		$this->db->query($create_shoutbox);		
	}
	/*Create Poll Database*/ 
	public function checkPolls(){
		$create_poll = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."news_poll` (
		`poll_id` int(11) NOT NULL auto_increment, 
		`date_added` datetime NOT NULL default '0000-00-00 00:00:00', 
		PRIMARY KEY  (`poll_id`)) 
		ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;";
		$this->db->query($create_poll);
		$create_poll_descriptions = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."news_poll_description` (
		`poll_id` int(11) NOT NULL, 
		`language_id` int(11) NOT NULL, 
		`question` varchar(255) collate utf8_bin NOT NULL, 
		`answer_1` varchar(255) collate utf8_bin NOT NULL,
		`answer_2` varchar(255) collate utf8_bin NOT NULL,
		`answer_3` varchar(255) collate utf8_bin NOT NULL,
		`answer_4` varchar(255) collate utf8_bin NOT NULL,
		`answer_5` varchar(255) collate utf8_bin NOT NULL,
		`answer_6` varchar(255) collate utf8_bin NOT NULL,
		`answer_7` varchar(255) collate utf8_bin NOT NULL,
		`answer_8` varchar(255) collate utf8_bin NOT NULL,
		`answer_9` varchar(255) collate utf8_bin NOT NULL,
		`answer_10` varchar(255) collate utf8_bin NOT NULL,
		`answer_11` varchar(255) collate utf8_bin NOT NULL,
		`answer_12` varchar(255) collate utf8_bin NOT NULL,
		`answer_13` varchar(255) collate utf8_bin NOT NULL,
		`answer_14` varchar(255) collate utf8_bin NOT NULL,
		`answer_15` varchar(255) collate utf8_bin NOT NULL,
		PRIMARY KEY  (`poll_id`,`language_id`), 
		KEY `question` (`question`)) 
		ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;";
		$create_poll_reactions = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."news_poll_reactions` 
		(`poll_reaction_id` int(11) NOT NULL auto_increment, 
		`poll_id` int(11) NOT NULL, 
		`answer` int(11) NOT NULL, 
		PRIMARY KEY  (`poll_reaction_id`)) 
		ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;";
		$create_poll_to_store = "CREATE TABLE IF NOT EXISTS `".DB_PREFIX."news_poll_to_store` (
		`poll_id` int(11) NOT NULL, 
		`store_id` int(11) NOT NULL, 
		PRIMARY KEY  (`poll_id`, `store_id`)) 
		ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;";
		
		$this->db->query($create_poll_descriptions);
		$this->db->query($create_poll_reactions);
		$this->db->query($create_poll_to_store);
	}
}
?>